package com.elitel.frame.business.service.impl;

import com.alibaba.fastjson.JSON;
import com.elitel.frame.base.component.CacheUtil;
import com.elitel.frame.base.component.RedisCacheComponent;
import com.elitel.frame.base.component.RedisCacheSingleTBServer;
import com.elitel.frame.base.component.RedisKey;
import com.elitel.frame.base.service.BaseFieldaddonsService;
import com.elitel.frame.business.dao.ext.SingleTableServerMapper;
import com.elitel.frame.business.service.SingleTableServerService;
import com.elitel.frame.business.service.TableFieldCommService;
import com.elitel.frame.business.service.workflow.SingleTableQueryWorkflow;
import com.elitel.frame.main.entity.CfgDb;
import com.elitel.frame.main.entity.CfgExtend;
import com.elitel.frame.main.entity.CfgField;
import com.elitel.frame.main.entity.CfgTable;
import com.elitel.frame.main.entity.vo.PageResponseVo;
import com.elitel.frame.main.entity.vo.TableParams;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.UUID;
import java.util.concurrent.ConcurrentHashMap;

@Service
public class SingleTableServerServiceImpl implements SingleTableServerService {
    private static final Logger logger = LoggerFactory.getLogger(SingleTableServerServiceImpl.class);

    @Autowired
    private SingleTableServerMapper singleTableServerMapper;
    @Autowired
    private RedisCacheComponent redisCacheComponent;
    @Autowired
    private RedisCacheSingleTBServer redisCacheSingleTBServer;
    @Autowired
    private TableFieldCommService tableFieldCommService;
    @Autowired
    private RedisKey redisKey;
    //  @Autowired private RedisCacheService redisCacheService;
    @Autowired
    private CacheUtil cacheUtil;
    @Autowired
    private CacheServiceData cacheServiceData;

    @Autowired
    private SingleTableQueryWorkflow singleTableQueryWorkflow;

    @Value("${cache.dataredis.library:#{2}}")
    private int rdi;

    @Value("${spring.jackson.date-format}")
    private String dateformat;

    @Autowired
    private BaseFieldaddonsService baseFieldaddonsService;

    private Map<String, Object> maplock = new ConcurrentHashMap<>();

    private String rediscount = "_count";

    @Override
    public Long singleTBCount(CfgDb prop, String count_sql) {
        Long count = singleTableServerMapper.singleTBCount(count_sql);
        return count;
    }

    @Override
    public PageResponseVo<Map<String, Object>> querySingleTBListPagination(
            CfgDb prop,
            CfgExtend cfgExtend,
            String pageNO,
            String pageSize,
            Map<String,Object> passConditionMap,
            String refCache) {
        PageResponseVo<Map<String, Object>> pageResponse = new PageResponseVo<Map<String, Object>>();
        if (cfgExtend.getSyscatch().equals(1)) {
            // 从缓存中查询数据
            pageResponse = queryDataByPageRedis(prop, cfgExtend, passConditionMap.toString(), pageNO, pageSize, refCache);
        } else if (cfgExtend.getSyscatch().equals(0)) {
            // 直接查询数据库
            pageResponse = queryDataByPage(prop, cfgExtend, pageNO, pageSize, passConditionMap);
        }

        return pageResponse;
    }

    /**
     * show 从缓存中分页查询数据 create by guoyanfei on 2018/12/19
     *
     * @param cfgExtend 单表查询服务配置信息
     * @param conQuery  查询条件
     * @param pageNO    当前页
     * @param pageSize  页大小
     * @return 缓存数据
     */
    private PageResponseVo<Map<String, Object>> queryDataByPageRedis(
            CfgDb prop,
            CfgExtend cfgExtend,
            String conQuery,
            String pageNO,
            String pageSize,
            String refCache) {
        PageResponseVo<Map<String, Object>> pageResponse = new PageResponseVo<Map<String, Object>>();
        List<Map<String, Object>> responseData = new ArrayList<>();
        Map<String, String> condion = null;
        if (null != conQuery && !conQuery.equals("")) {
            condion = (Map<String, String>) JSON.parse(conQuery);
        }

        // 计算数据缓存页数(缓存默认一页500条记录)
        Integer tatol = Integer.valueOf(pageNO) * Integer.valueOf(pageSize);
        Integer page = (int) Math.ceil(tatol / 500.0);
        String pageNum = page.toString();
        // 获取普通redisKey
        String redisKeystr = redisKey.generate(cfgExtend.getGuid(), condion, "1", "0", pageNum);
        List<Map<String, Object>> redisData = new ArrayList<>();
        setMaplock(redisKeystr);

        if (refCache.equals("true")) {
            // 强制刷新
//      redisCacheService.delObject(redisKeystr, rdi);
            cacheUtil.delCache(redisKeystr);
            redisData = queryDataAddRedis(redisKeystr, prop, cfgExtend, conQuery, pageNum, "500");
        } else {
//            if (redisCacheService.stringExists(redisKeystr, rdi)) {
//                // 获取普通缓存数据
//                redisData = queryDataBykey(redisKeystr);
//
//                logger.warn("===========Begin单表服务分页查询==============");
//                logger.warn("服务ID：" + cfgExtend.getGuid());
//                logger.warn("redisKey：" + redisKeystr);
//                logger.warn("===========End单表服务查询==============");
//            } else {
                // 从数据库中查询数据并添加到缓存
                redisData = queryDataAddRedis(redisKeystr, prop, cfgExtend, conQuery, pageNum, "500");
//            }
        }

        // 查询共有多少条记录
//        Long count = queryRedisCount(redisKeystr);
        Long count =  cacheServiceData.queryRedisCount(redisKeystr);

        // 截取缓存数据
        if (redisData != null && redisData.size() > 0) {
            Integer beginnum =
                    (Integer.valueOf(pageNO) - 1) * Integer.valueOf(pageSize) + 1 - ((page - 1) * 500);
            Integer endnum = Integer.valueOf(pageNO) * Integer.valueOf(pageSize) - ((page - 1) * 500);
            if (redisData.size() < endnum) {
                for (int i = beginnum - 1; i < redisData.size(); i++) {
                    responseData.add(redisData.get(i));
                }
            } else {
                for (int i = beginnum - 1; i < endnum; i++) {
                    responseData.add(redisData.get(i));
                }
            }

            pageResponse.setRows(responseData);
            pageResponse.setTotal(count.intValue());
        }

        return pageResponse;
    }

    /**
     * show 设置锁对象 create by guoyanfei on 2018/12/28
     */
    private void setMaplock(String redisKeystr) {
        if (maplock != null && maplock.size() > 0) {
            boolean flag = maplock.containsKey(redisKeystr);
            if (!flag) {
                // 不存在
                maplock.put(redisKeystr, redisKeystr);
            }
        } else {
            // 不存在
            maplock.put(redisKeystr, redisKeystr);
        }
    }

    /**
     * show 通过redisKey查询数据 create by guoyanfei on 2018/12/20
     *
     * @param redisKey
     * @return
     */
//    private List<Map<String, Object>> queryDataBykey(String redisKey) {
//        List<Map<String, Object>> redisData = new ArrayList<>();
//        String cfgStr = redisCacheService.getObject(redisKey, rdi);
//        List<Object> dataobject = JSON.parseArray(cfgStr);
//        if (dataobject != null && dataobject.size() > 0) {
//            for (Object object : dataobject) {
//                Map<String, Object> ret = (Map<String, Object>) object;
//                redisData.add(ret);
//            }
//        }
//        return redisData;
//    }

    /**
     * show 从数据库中分页查询数据并添加到缓存 create by guoyanfei on 2018/12/21
     *
     * @param redisKeystr redisKey
     * @param prop        数据源
     * @param cfgExtend   单表配置信息
     * @param conQuery    查询条件
     * @param pageNum     缓存当前页数
     * @param pageSize    缓存每页大小
     * @return 数据结果
     */
    private List<Map<String, Object>> queryDataAddRedis(
            String redisKeystr,
            CfgDb prop,
            CfgExtend cfgExtend,
            String conQuery,
            String pageNum,
            String pageSize) {
        List<Map<String, Object>> redisData = new ArrayList<>();
        synchronized (maplock.get(redisKeystr)) {
//      if (redisCacheService.stringExists(redisKeystr, rdi)) {
//        // 获取普通缓存数据
//        redisData = queryDataBykey(redisKeystr);
//      } else {
//        PageResponseVo<Map<String, Object>> pageResponse =
//            queryDataByPage(prop, cfgExtend, pageNum, "500", conQuery);
//        List<Map<String, Object>> viewData = pageResponse.getRows();
//        String extendStr = JSON.toJSONStringWithDateFormat(viewData, dateformat);
//        // 过期时间分钟转换秒
//        long timeOut = Long.valueOf(cfgExtend.getCatchtime()) * 60;
//        redisCacheService.saveObjectTimeOut(redisKeystr, extendStr, rdi, timeOut);
//
//        // 从缓存中查询数据
//        redisData = queryDataBykey(redisKeystr);
//
//        // 设置总条数
//        setRedisCount(redisKeystr, prop, cfgExtend, conQuery);
//      }
            redisData = cacheServiceData.queryDataAddRedisSingle(redisKeystr, prop, cfgExtend, conQuery, pageNum, "500");
            cacheServiceData.setRedisCount(redisKeystr, prop, cfgExtend, conQuery);
        }

        return redisData;
    }

    /**
     * show 直接从数据库分页查询数据 create by guoyanfei on 2018/12/19
     *
     * @param cfgDb      CfgDb实体
     * @param cfgExtend 单表配置信息实体
     * @param pageNO    当前页
     * @param pageSize  页大小
     * @param passConditionMap  查询条件
     * @return 查询返回结果
     */
    @Override
    public PageResponseVo queryDataByPage(CfgDb cfgDb, CfgExtend cfgExtend, String pageNO, String pageSize, Map<String, Object> passConditionMap) {
        return singleTableQueryWorkflow.querySingleTableList(cfgDb, cfgExtend, pageNO, pageSize, passConditionMap);
    }

    /**
     * * show 从缓存中不分页查询数据 create by guoyanfei on 2018/12/20
     *
     * @param prop      数据源
     * @param cfgExtend 单表查询配置信息
     * @param conQuery  查询条件
     * @return 查询结果
     */
    private PageResponseVo queryDataNoPageRedis(CfgDb prop, CfgExtend cfgExtend, String conQuery, String refCache) {
        PageResponseVo pageResponse = new PageResponseVo();
        List<Map<String, Object>> responseData = new ArrayList<>();
        Map<String, String> condion = null;
        if (null != conQuery && !conQuery.equals("")) {
            condion = (Map<String, String>) JSON.parse(conQuery);
        }
        // 获取普通redisKey
        String redisKeystr = redisKey.generate(cfgExtend.getGuid(), condion, "1", "0", "");
        setMaplock(redisKeystr);
        // 获取普通缓存数据
//        if (redisCacheService.stringExists(redisKeystr, rdi)) {
//            if (refCache.equals("false")) {
//                // 直接读取缓存数据
//                responseData = queryDataBykey(redisKeystr);
//
//                logger.warn("===========Begin单表服务不分页查询==============");
//                logger.warn("服务ID：" + cfgExtend.getGuid());
//                logger.warn("redisKey：" + redisKeystr);
//                logger.warn("===========End单表服务查询==============");
//            } else if (refCache.equals("true")) {
//                // 强制刷新缓存数据
////                redisCacheService.delObject(redisKeystr, rdi);
//                cacheUtil.delCache(redisKeystr);
//                responseData = queryDataNoPageAddRedis(redisKeystr, prop, cfgExtend, conQuery);
//            }
//        } else {
            // 不存在缓存数据更新缓存
            responseData = queryDataNoPageAddRedis(redisKeystr, prop, cfgExtend, conQuery);
//        }

        // 查询共有多少条记录
//        Long count = queryRedisCount(redisKeystr);
        Long count = cacheServiceData.queryRedisCount(redisKeystr);

        pageResponse.setRows(responseData);
        pageResponse.setTotal(count.intValue());

        return pageResponse;
    }

    /**
     * show 从缓存中查询数据 create by guoyanfei on 2019/01/28
     *
     * @param rediskeystr
     * @return 总条数
     */
//    private Long queryRedisCount(String rediskeystr) {
//        Long count = 0L;
//        if (redisCacheService.stringExists(rediskeystr + rediscount, rdi)) {
//            String strcount = redisCacheService.getObject(rediskeystr + rediscount, rdi);
//            if (!StringUtils.isEmpty(strcount)) {
//                try {
//                    count = Long.valueOf(strcount);
//                } catch (Exception e) {
//                    count = 0L;
//                }
//            }
//        }
//
//        return count;
//    }

    /**
     * show 把查询的数据总条数存入redis create by guoyanfei on 2019/01/28
     *
     * @param rediskeystr
     * @param prop
     * @param cfgExtend
     * @param conQuery
     */
//    private void setRedisCount(String rediskeystr, CfgDb prop, CfgExtend cfgExtend, String conQuery) {
//        Long count = queryDataTotal(prop, cfgExtend, conQuery);
//        long timeOut = Long.valueOf(cfgExtend.getCatchtime()) * 60;
//        redisCacheService.saveObjectTimeOut(rediskeystr + rediscount, count.toString(), rdi, timeOut);
//    }

    /**
     * * show 不分页查询数据库添加到缓存
     *
     * @param
     * @param prop      数据源
     * @param cfgExtend 单表配置信息
     * @param conQuery  查询条件
     * @return 数据结果
     */
    private synchronized List<Map<String, Object>> queryDataNoPageAddRedis(
            String redisKeystr, CfgDb prop, CfgExtend cfgExtend, String conQuery) {
        List<Map<String, Object>> responseData = new ArrayList<>();
        synchronized (maplock.get(redisKeystr)) {
//            if (redisCacheService.stringExists(redisKeystr, rdi)) {
//                responseData = queryDataBykey(redisKeystr);
//            } else {
//                responseData = querySingleTBList(prop, cfgExtend, conQuery).getRows();
//                String extendStr = JSON.toJSONString(responseData);
//                // 过期时间分钟转换秒
//                long timeOut = Long.valueOf(cfgExtend.getCatchtime()) * 60;
//                redisCacheService.saveObjectTimeOut(redisKeystr, extendStr, rdi, timeOut);
//                responseData = queryDataBykey(redisKeystr);
//
//                // 设置总条数
//                setRedisCount(redisKeystr, prop, cfgExtend, conQuery);
//            }
            responseData = cacheServiceData.queryDataNoPageAddRedis(redisKeystr, prop, cfgExtend, conQuery);
            cacheServiceData.setRedisCount(redisKeystr, prop, cfgExtend, conQuery);
        }

        return responseData;
    }

    /**
     * * show 查询数据共多少条记录 create by guoyanfei on 2018/12/21
     *
     * @param prop      数据源
     * @param cfgExtend 单表查询配置信息
     * @param conQuery  查询条件
     * @return 数据结果
     */
    public Long queryDataTotal(CfgDb prop, CfgExtend cfgExtend, String conQuery) {
        // 查询共有多少条记录
        // 设置表名称
        String tables = tableFieldCommService.getTablenameTxt(prop, cfgExtend.getDtConfigId());
        // 条件的集合
        Map pays = null;
        if (null != conQuery && !conQuery.equals("")) {
            pays = (Map) JSON.parse(conQuery);
        }
        // 设置显示字段
        String fields =
                tableFieldCommService.getFieldnameByserviceID(
                        prop, cfgExtend.getGuid(), "1", cfgExtend.getDtConfigId(), "");
        // 设置参数条件
        String sqltxt =
                tableFieldCommService.getQuerysqlByserviceId(prop, cfgExtend.getGuid(), "1", "");
        String wherecontent = tableFieldCommService.processSqlByFreemarker(prop, sqltxt, pays);
        String whereSql = "";
        if (wherecontent != null && wherecontent.isEmpty() == false) {
            whereSql += wherecontent;
        }
        // 总条数sql语句
        String count_sql =
                "select count(*) from (SELECT * FROM( SELECT "
                        + fields
                        + " FROM "
                        + tables
                        + ")b where 1=1 "
                        + whereSql
                        + ") a ";
        Long count = singleTableServerMapper.singleTBCount(count_sql);
        return count;
    }

    @Override
    public Boolean insertSingleTable(CfgDb prop, CfgExtend cfgExtend, String singleTBArrayJson) {
        int count = 0;
        List<CfgField> cfgList = null;
        CfgTable cfgTable = null;
        if (null != cfgExtend) {
            // 查询表字段信息
            cfgList = redisCacheComponent.cfgFieldRedisCache(cfgExtend.getDtConfigId());
            // 查询表名及数据源标识
            cfgTable =
                    redisCacheComponent.cfgTableRedisCache(cfgExtend.getDtConfigId(), cfgExtend.getDbKey());
        }
        // {"DB_KEY":"zhangsan","DB_NAME":"lisi","DB_VERSION":"wangwu","DB_URL":"maliu","DB_USER":"maliu","DB_PWD":"maliu","DB_MARK":"maliu"}
        // "{\"DB_KEY\":\"zhangsan\",\"DB_NAME\":\"lisi\",\"DB_VERSION\":\"wangwu\",\"DB_URL\":\"maliu\",\"DB_USER\":\"maliu\",\"DB_PWD\":\"maliu\",\"DB_MARK\":\"maliu\"}"
        TableParams params = new TableParams();
        if (null != cfgTable && cfgList.size() > 0) {
            Map maps = (Map) JSON.parse(singleTBArrayJson);
            for (CfgField cf : cfgList) {
                // 日期格式转换
                if (null != cf.getDtType() && cf.getDtType().equals("date")) {
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh24:mi");
                    try {
                        maps.put(cf.getDfName(), sdf.parse(maps.get(cf.getDfName()).toString()));
                    } catch (ParseException e) {
                        e.printStackTrace();
                    }
                    break;
                }

                // 主键自动生成
                if (cf.getDfIsprimarykey().equals(1)) {
                    String primarykey = maps.get(cf.getDfName()).toString();
                    if (primarykey == null || primarykey.equals("")) {
                        maps.put(cf.getDfName(), UUID.randomUUID().toString());
                    }
                }
            }
            params.setInsertMap(maps);
            params.setTables(cfgTable.getDtName());
            count = singleTableServerMapper.insertSingleTable(params);
        }
        return count > 0;
    }

    @Override
    public Boolean deleteBatchSingleTable(CfgDb prop, CfgExtend cfgExtend, String singleIDs) {
        int count = 0;
        List<CfgField> cfgList = null;
        CfgTable cfgTable = null;
        if (null != cfgExtend) {
            // 查询表字段信息
            cfgList = redisCacheComponent.cfgFieldRedisCache(cfgExtend.getDtConfigId());
            // 查询表名及数据源标识
            cfgTable =
                    redisCacheComponent.cfgTableRedisCache(cfgExtend.getDtConfigId(), cfgExtend.getDbKey());
        }
        TableParams params = new TableParams();
        if (null != cfgTable && cfgList.size() > 0) {
            params.setTables(cfgTable.getDtName());
            List<String> list = new ArrayList<String>();
            String[] id = singleIDs.split(",");
            for (int i = 0; i < id.length; i++) {
                if (id[i] != null && !"".equals(id[i])) {
                    list.add(id[i].trim());
                }
            }
            params.setDeleteList(list);
            for (CfgField cfg : cfgList) {
                if (cfg.getDfIsprimarykey() == 1) {
                    params.setDeleteCoulumnName(cfg.getDfName());
                    break;
                }
            }
            count = singleTableServerMapper.deleteBatchSingleTable(params);
        }
        return count > 0;
    }

    @Override
    public Boolean updateSingleTable(CfgDb prop, CfgExtend cfgExtend, String singleTBArrayJson) {
        int count = 0;
        List<CfgField> cfgList = null;
        CfgTable cfgTable = null;
        if (null != cfgExtend) {
            // 查询表字段信息
            cfgList = redisCacheComponent.cfgFieldRedisCache(cfgExtend.getDtConfigId());
            // 查询表名及数据源标识
            cfgTable =
                    redisCacheComponent.cfgTableRedisCache(cfgExtend.getDtConfigId(), cfgExtend.getDbKey());
        }
        StringBuffer str = new StringBuffer("update ");
        StringBuffer updateBuffer = new StringBuffer();
        StringBuffer whereSql = new StringBuffer(" where ");
        String primaryKey = "";
        if (null != cfgTable && cfgList.size() > 0) {
            Map<String, Object> maps = (Map) JSON.parse(singleTBArrayJson);
            // 获取主键及条件
            for (CfgField cfg : cfgList) {
                if (cfg.getDfIsprimarykey() == 1) {
                    primaryKey = cfg.getDfName();
                    continue;
                }
                if (maps.get(cfg.getDfName()) == null) {
                    updateBuffer.append(cfg.getDfName()).append(" = ").append(" null ").append(",");
                } else {
                    // 判断该字段是否是时间格式
                    if (null != cfg.getDtType() && cfg.getDtType().equals("date")) {
                        // 按数据库类型来选择不同的时间语句
                        switch (prop.getDbVersion()) {
                            case "ORACLE":
                                updateBuffer
                                        .append(cfg.getDfName())
                                        .append(" = ")
                                        .append("to_date('" + maps.get(cfg.getDfName()) + "','yyyy-MM-dd hh24:mi:ss')")
                                        .append(",");
                                break;

                            case "MYSQL":
                                updateBuffer
                                        .append(cfg.getDfName())
                                        .append(" = ")
                                        .append(maps.get(cfg.getDfName()))
                                        .append(",");
                                break;
                            case "SQLSERVER":
                                updateBuffer
                                        .append(cfg.getDfName())
                                        .append(" = ")
                                        .append(maps.get(cfg.getDfName()))
                                        .append(",");
                                break;
                        }
                    } else {
                        updateBuffer
                                .append(cfg.getDfName())
                                .append(" = ")
                                .append("'" + maps.get(cfg.getDfName()) + "'")
                                .append(",");
                    }
                }
            }
            str.append(cfgTable.getDtName()).append(" set ");

            whereSql.append(primaryKey).append(" = ").append("'" + maps.get(primaryKey) + "'");
            str.append(updateBuffer.substring(0, updateBuffer.length() - 1)).append(whereSql);
            count = singleTableServerMapper.updateSingleTable(str.toString());
        }
        return count > 0;
    }
}
